﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using Dapper;
using Dapper.Contrib.Extensions;

namespace EC.Application.Impl
{
    using EC.Application.Interface;
    using EC.Entity;
    using EC.Entity.Enum;
    using EC.Entity.Query;
    using EC.Entity.Request.BankCard;
    using EC.Entity.Response.BankCard;
    using EC.Entity.Response.Merchant;
    using EC.Entity.Tables;

    /// <summary>
    /// 银行卡接口实现
    /// </summary>
    public class BankCardServiceImpl: BankCardService
    {
        #region 注入
        /// <summary>
        /// 数据接口
        /// </summary>
        private readonly IDBSession _dbSession;

        /// <summary>
        /// 字段
        /// </summary>
        private readonly string _files = "Id,MerchantId,(select Name from merchant where Id=bankcard.MerchantId) as ProxyMerchantName,(select Name from merchant where Id=bankcard.CreatedByMerchantId) as ProxyGroup,CardType,BankName,BankCode,BankCardNumber,BankCardName,LoginName,PasswordLogin,PasswordPay,PasswordShield,Balance,BalanceUpdateTime,VmClientId,VmClientIdUpdateDate,PaymentStart,PaymentEnd,Remark,Frozen,UpperLimit,Status,CreatedByMerchantId,CreatedBy,CreatedDate";
        /// <summary>
        /// 数据库上下文
        /// </summary>
        /// <param name="context">数据库上下文</param>
        public BankCardServiceImpl(IDBSession dbSession)
        {
            _dbSession = dbSession;
        }
        #endregion

        /// <summary>
        /// 获取
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public BankCardResponse Get(int? id)
        {
            var bankCardResponse = new BankCardResponse();
            if(id.HasValue)
            {
                bankCardResponse = _dbSession.DbConnection.QueryFirst<BankCardResponse>($"select * from bankcard where Id=@Id", new { Id = id });

            }

            //所有商户
            //var merchants = _dbSession.DbConnection.Query<MerchantResponse>("SELECT * FROM merchant WHERE Status = @Status", new { Status = (int)MerchantStatus.启用 }).ToList();
            //if (merchants != null && merchants.Any())
            //{
            //    bankCardResponse.MerchantResponse = merchants;
            //}

            return bankCardResponse;
        }

        /// <summary>
        /// 授权
        /// </summary>
        /// <returns></returns>
        public JResult Auth(int id, sbyte status)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var bigclass = _dbSession.DbConnection.QueryFirst<BankCardResponse>($"select * from bankcard where Id=@Id", new { Id = id });
                if (bigclass == null)
                {
                    throw new Exception("银行卡记录不存在.");
                }

                var updateSql = "UPDATE bankcard SET Status=@Status WHERE Id=@Id";

                int result = _dbSession.DbConnection.Execute(updateSql, new { Status = status, Id = id });
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }

                response.Success = true;
                response.Message = "操作成功.";
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }
            return response;
        }

        /// <summary>
        /// 虚拟机编码
        /// </summary>
        /// <returns></returns>
        public JResult VmClientId(VmClientIdRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var model = _dbSession.DbConnection.QueryFirst<BankCardResponse>($"select * from bankcard where Id=@Id", new { Id = request.Id });
                if (model == null)
                {
                    throw new Exception("银行卡记录不存在.");
                }

                var updateSql = "UPDATE bankcard SET VmClientId=@VmClientId,Remark=@Remark WHERE Id=@Id";

                int result = _dbSession.DbConnection.Execute(updateSql, new { VmClientId = request.VmClientId, Remark = request.Remark, Id = model.Id });
                if (result <= 0)
                {
                    throw new Exception("更新失败");
                }

                response.Success = true;
                response.Message = "操作成功.";
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
            }
            return response;
        }

        /// <summary>
        /// 新增、编辑
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public JResult SaveBankCard(SaveBankCardRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var model = _dbSession.DbConnection.QueryFirst<BankCard>($"select * from bankcard where Id=@Id", new { Id = request.Id });
                if (model == null)
                {
                    throw new Exception("银行卡记录不存在.");
                }

                model.CardType = request.CardType.Value;
                model.MerchantId = request.CardType.Value == (int)CardType.备用卡 ? 0 : request.MerchantId;
                model.Frozen = request.Frozen.Value;
                model.Status = request.Status.Value;
                model.UpperLimit = request.UpperLimit;
                //更新
                _dbSession.DbConnection.Update<BankCard>(model);

                response.Success = true;
            }
            catch(Exception ex)
            {
                response.Message = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 保存、修改
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public JResult SaveStockCard(SaveStockCardRequest request)
        {
            var response = new JResult()
            {
                Success = false
            };

            if (request.Id > 0)
            {
                //var bankCardExist = _dbSession.DbConnection.Query<BankCard>($"SELECT * FROM bankcard WHERE BankCardNumber=@BankCardNumber AND Id!=@Id", new { BankCardNumber = request.BankCardNumber, request.Id });
                //if (bankCardExist != null && bankCardExist.Any())
                //{
                //    throw new Exception("银行卡号已存在.");
                //}

                var bankcard = _dbSession.DbConnection.QueryFirst<BankCard>($"select * from bankcard where Id=@Id", new { Id = request.Id });
                if (bankcard == null)
                {
                    throw new Exception("银行卡记录不存在.");
                }

                bankcard.BankName = request.BankName.Trim();
                bankcard.BankCode = request.BankCode.Trim();
                bankcard.BankCardNumber = request.BankCardNumber.Trim();
                bankcard.BankCardName = request.BankCardName.Trim();
                bankcard.LoginName = request.LoginName.Trim();
                bankcard.PasswordLogin = request.PasswordLogin.Trim();
                //更新
                _dbSession.DbConnection.Update<BankCard>(bankcard);

                response.Success = true;
            }
            else
            {
                //var bankCardExist = _dbSession.DbConnection.Query<BankCard>($"SELECT * FROM bankcard WHERE BankCardNumber=@BankCardNumber", new { BankCardNumber = request.BankCardNumber });
                //if (bankCardExist != null && bankCardExist.Any())
                //{
                //    throw new Exception("银行卡号已存在.");
                //}

                if (request.UserId <= 0)
                {
                    throw new Exception("用户编号参数错误.");
                }

                if (request.MerchantId<=0)
                {
                    throw new Exception("商户编号参数错误.");
                }

                var bankcard = new BankCard()
                {
                    MerchantId = request.MerchantId,
                    CardType = (int)CardType.备用卡,
                    BankName = request.BankName.Trim(),
                    BankCode = request.BankCode.Trim(),
                    BankCardNumber = request.BankCardNumber.Trim(),
                    BankCardName = request.BankCardName.Trim(),
                    LoginName = request.LoginName.Trim(),
                    PasswordLogin = request.PasswordLogin.Trim(),
                    CreatedByMerchantId = request.MerchantId,
                    CreatedBy = request.UserId,
                    CreatedDate = DateTime.Now
                };

                //新增
                long resultId = _dbSession.DbConnection.Insert<BankCard>(bankcard);

                if (resultId <= 0)
                {
                    throw new Exception("新增银行卡失败.");
                }
                response.Success = true;
            }
            return response;
        }

        /// <summary>
        /// 库存卡查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public PagedList<StockCardQueryResponse> StockCardPager(StockCardQuery query)
        {
            var currentPageIndex = 0;

            if (query.CurrentPageIndex.Value > 0)
            {
                currentPageIndex = (query.CurrentPageIndex.Value - 1) * query.PageSize.Value;
            }

            var tableName = "bankcard";
            var orderby = "ORDER BY Id DESC";
            var where = $"where CreatedBy={query.UserId} and CardType={query.CardType}";

            #region 条件
            //if (!string.IsNullOrEmpty(query.Name))
            //{
            //    where += string.Format(" And Name like'%{0}%'", query.Name);
            //}
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            sb.AppendFormat("SELECT * FROM {0} {1} {2} LIMIT {3},{4};", tableName, where, orderby, currentPageIndex, query.PageSize.Value);

            using (var multi = _dbSession.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<StockCardQueryResponse>();

                return new PagedList<StockCardQueryResponse>()
                {
                    TData = list,
                    CurrentPageIndex = query.CurrentPageIndex.Value,
                    TotalCount = count
                };
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public PagedList<BankCardQueryResponse> GetPager(BankCardQuery query)
        {
            var currentPageIndex = 0;

            if (query.CurrentPageIndex.Value > 0)
            {
                currentPageIndex = (query.CurrentPageIndex.Value - 1) * query.PageSize.Value;
            }

            var tableName = "bankcard";
            var orderby = "ORDER BY BalanceUpdateTime DESC";
            var where = $"where CreatedBy={query.UserId} and CardType in({string.Join(", ", query.CardType)}) and Status in({string.Join(", ", query.Status)})";

            #region 条件
            //if (!string.IsNullOrEmpty(query.Name))
            //{
            //    where += string.Format(" And Name like'%{0}%'", query.Name);
            //}
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", _files, tableName, where, orderby, currentPageIndex, query.PageSize.Value);

            using (var multi = _dbSession.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<BankCardQueryResponse>();

                return new PagedList<BankCardQueryResponse>()
                {
                    TData = list,
                    CurrentPageIndex = query.CurrentPageIndex.Value,
                    TotalCount = count
                };
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public PagedList<BankCardQueryResponse> GetSyPager(BankCardQuery query)
        {
            var currentPageIndex = 0;

            if (query.CurrentPageIndex.Value > 0)
            {
                currentPageIndex = (query.CurrentPageIndex.Value - 1) * query.PageSize.Value;
            }

            var tableName = "bankcard";
            var orderby = "ORDER BY Status desc ";
            var where = $"where CardType in({string.Join(", ", query.CardType)}) and Status in({string.Join(", ", query.Status)})";

            #region 条件
            if (!string.IsNullOrEmpty(query.BankCardNumber))
            {
                where += string.Format(" And BankCardNumber ={0}", query.BankCardNumber);
            }
            if (query.CreatedByMerchantId > 0)
            {
                where += string.Format(" And CreatedByMerchantId ={0}", query.CreatedByMerchantId);
            }
            if (query.MerchantId>0)
            {
                where += string.Format(" And MerchantId ={0}", query.MerchantId);
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", _files, tableName, where, orderby, currentPageIndex, query.PageSize.Value);

            using (var multi = _dbSession.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<BankCardQueryResponse>();

                return new PagedList<BankCardQueryResponse>()
                {
                    TData = list,
                    CurrentPageIndex = query.CurrentPageIndex.Value,
                    TotalCount = count
                };
            }
        }
    }
}
